﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace VioletDevelopmentLaboratory.Net.Data
{
	public class SqlClientAgent
	{
		private SqlClientAgent() { }

        public static DataSet ExecuteDataSet(String connectionString, CommandType commandType, String commandText, out object returnValue, params SqlParameter[] parameters)
		{
            returnValue = null;

			SqlConnection connection = new SqlConnection(connectionString);

			SqlCommand command = connection.CreateCommand();
			command.CommandType = commandType;
			command.CommandText = commandText;

			foreach (SqlParameter parameter in parameters)
			{
				command.Parameters.Add(parameter);
			}

            SqlParameter returnValueParameter = new SqlParameter();
            returnValueParameter.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValueParameter);

			SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

			DataSet result = new DataSet();

			try
			{
				dataAdapter.Fill(result);
                returnValue = returnValueParameter.Value;
			}
			finally
			{
				dataAdapter.Dispose();
				dataAdapter = null;
				command.Dispose();
				command = null;

				if ((connection != null) && (connection.State != ConnectionState.Closed))
				{
					connection.Close();
					connection.Dispose();
					connection = null;
				}
			}
			return result;
		}

        public static DataSet ExecuteDataSet(String connectionString, CommandType commandType, String commandText, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

            DataSet result = new DataSet();

            try
            {
                dataAdapter.Fill(result);
            }
            finally
            {
                dataAdapter.Dispose();
                dataAdapter = null;
                command.Dispose();
                command = null;

                if ((connection != null) && (connection.State != ConnectionState.Closed))
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }
            return result;
        }

        public static Boolean ExecuteExists(String connectionString, CommandType commandType, String commandText, out object returnValue, params SqlParameter[] parameters)
		{
            returnValue = null;

			SqlConnection connection = new SqlConnection(connectionString);

			SqlCommand command = connection.CreateCommand();
			command.CommandType = commandType;
			command.CommandText = commandText;

			foreach (SqlParameter parameter in parameters)
			{
				command.Parameters.Add(parameter);
			}

            SqlParameter returnValueParameter = new SqlParameter();
            returnValueParameter.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValueParameter);

			SqlDataReader dataReader = null;

			Boolean result = false;

			try
			{
				connection.Open();
				dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
				result = dataReader.HasRows;
				dataReader.Close();
                returnValue = returnValueParameter.Value;
			}
			finally
			{
				if (!((dataReader == null) || dataReader.IsClosed))
				{
					dataReader.Close();
					dataReader.Dispose();
					dataReader = null;
				}

				command.Dispose();
				command = null;

				if (((connection != null) && (connection.State != ConnectionState.Closed)) && (dataReader == null))
				{
					connection.Close();
					connection.Dispose();
					connection = null;
				}
			}
			return result;
		}

        public static Boolean ExecuteExists(String connectionString, CommandType commandType, String commandText, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            SqlDataReader dataReader = null;

            Boolean result = false;

            try
            {
                connection.Open();
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                result = dataReader.HasRows;
                dataReader.Close();
            }
            finally
            {
                if (!((dataReader == null) || dataReader.IsClosed))
                {
                    dataReader.Close();
                    dataReader.Dispose();
                    dataReader = null;
                }

                command.Dispose();
                command = null;

                if (((connection != null) && (connection.State != ConnectionState.Closed)) && (dataReader == null))
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }
            return result;
        }

        public static Int32 ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, out object returnValue, params SqlParameter[] parameters)
		{
            returnValue = null;

			Int32 affectedRow = 0;

			SqlConnection connection = new SqlConnection(connectionString);

			SqlCommand command = connection.CreateCommand();
			command.CommandType = commandType;
			command.CommandText = commandText;

			foreach (SqlParameter parameter in parameters)
			{
				command.Parameters.Add(parameter);
			}

            SqlParameter returnValueParameter = new SqlParameter();
            returnValueParameter.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValueParameter);

			try
			{
				connection.Open();
				affectedRow = command.ExecuteNonQuery();
				connection.Close();
                returnValue = returnValueParameter.Value;
			}
			finally
			{
				command.Dispose();
				command = null;

				if ((connection != null) && (connection.State != ConnectionState.Closed))
				{
					connection.Close();
					connection.Dispose();
					connection = null;
				}
			}

			return affectedRow;
		}

        public static Int32 ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, params SqlParameter[] parameters)
        {
            Int32 affectedRow = 0;

            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            try
            {
                connection.Open();
                affectedRow = command.ExecuteNonQuery();
                connection.Close();
            }
            finally
            {
                command.Dispose();
                command = null;

                if ((connection != null) && (connection.State != ConnectionState.Closed))
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }

            return affectedRow;
        }

        public static SqlDataReader ExecuteReader(String connectionString, CommandType commandType, String commandText, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

			SqlDataReader dataReader = null;

			try
			{
				connection.Open();
				dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch
			{
				if (dataReader != null)
					dataReader.Close();

				connection.Close();

				dataReader = null;
			}

			return dataReader;
        }

        public static Object ExecuteScalar(String connectionString, CommandType commandType, String commandText, out object returnValue, params SqlParameter[] parameters)
		{
            returnValue = null;

			SqlConnection connection = new SqlConnection(connectionString);

			SqlCommand command = connection.CreateCommand();
			command.CommandType = commandType;
			command.CommandText = commandText;

			foreach (SqlParameter parameter in parameters)
			{
				command.Parameters.Add(parameter);
			}
            SqlParameter returnValueParameter = new SqlParameter();
            returnValueParameter.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValueParameter);

			Object result = null;
			
			try
			{
				connection.Open();
				result = command.ExecuteScalar();
				connection.Close();
                returnValue = returnValueParameter.Value;
			}
			finally
			{
				command.Dispose();
				command = null;
				
				if ((connection != null) && (connection.State != ConnectionState.Closed))
				{
					connection.Close();
					connection.Dispose();
					connection = null;
				}
			}
			
			return result;
		}

        public static Object ExecuteScalar(String connectionString, CommandType commandType, String commandText, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            Object result = null;

            try
            {
                connection.Open();
                result = command.ExecuteScalar();
                connection.Close();
            }
            finally
            {
                command.Dispose();
                command = null;

                if ((connection != null) && (connection.State != ConnectionState.Closed))
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }

            return result;
        }
    }
}
